﻿/*=================================================================
* 版权信息：Copyright (C) 2005-2011 捷点科技 All Rights Reserved.
* 文件名称：CNVP_NewsInfo.cs
* 创建标识：曾乃强 15158756652 2011-03-17 11:03:01
* 修改标识：
* 功能描述：
===================================================================*/
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using CNVP.Framework.Helper;
using CNVP.Framework;
using CNVP.Config;

namespace CNVP.JShop.DAL
{
    /// <summary>
    /// 对*CNVP_NewsInfo*操作数据库(针对SQL Server)的实现
    /// </summary>
    public class CNVP_NewsInfo
    {
       private string Prefix = DBConfig.Prefix;
        #region DAL
        public int Insert(Model.CNVP_NewsInfo obj)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("insert into  " + Prefix + "NewsInfo ([NewsClassID],[NewsTitle],[NewsContent],[AddTime],[UpdateTime],[IsShow],[ShowTime],[IsDelete],[HotLink],[SeoInfoID])");
            sql.Append("values(@NewsClassID,@NewsTitle,@NewsContent,@AddTime,@UpdateTime,@IsShow,@ShowTime,0,@HotLink,@SeoInfoID)");

            SqlParameter[] pra = {
                            new SqlParameter("@NewsClassID",obj.NewsClassID),
							new SqlParameter("@NewsTitle",obj.NewsTitle),
							new SqlParameter("@NewsContent",obj.NewsContent),
							new SqlParameter("@AddTime",obj.AddTime),
							new SqlParameter("@UpdateTime",obj.UpdateTime),
							new SqlParameter("@IsShow",obj.IsShow),
							new SqlParameter("@ShowTime",obj.ShowTime),
							new SqlParameter("@HotLink",obj.HotLink),
							new SqlParameter("@SeoInfoID",obj.SeoInfoID)
                                 };
            return DbHelper.ExecuteNonQuery(sql.ToString(), pra);
        }

        public int Update(Model.CNVP_NewsInfo obj)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("update " + Prefix + "NewsInfo set [NewsClassID] = @NewsClassID,[NewsTitle] = @NewsTitle,[NewsContent] = @NewsContent,[AddTime] = @AddTime,[UpdateTime] = @UpdateTime,[IsShow] = @IsShow,[ShowTime] = @ShowTime,[IsDelete] = @IsDelete,[HotLink] = @HotLink,[SeoInfoID] = @SeoInfoID where [ID] = @ID");

            SqlParameter[] pra = { 
                            new SqlParameter("@ID",obj.ID),
							new SqlParameter("@NewsClassID",obj.NewsClassID),
							new SqlParameter("@NewsTitle",obj.NewsTitle),
							new SqlParameter("@NewsContent",obj.NewsContent),
							new SqlParameter("@AddTime",obj.AddTime),
							new SqlParameter("@UpdateTime",obj.UpdateTime),
							new SqlParameter("@IsShow",obj.IsShow),
							new SqlParameter("@ShowTime",obj.ShowTime),
							new SqlParameter("@IsDelete",obj.IsDelete),
							new SqlParameter("@HotLink",obj.HotLink),
							new SqlParameter("@SeoInfoID",obj.SeoInfoID)
                                };
            return DbHelper.ExecuteNonQuery(sql.ToString(), pra);
        }

        public int Delete(Model.CNVP_NewsInfo obj)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("delete from " + Prefix + "NewsInfo where [ID] = @ID");
            SqlParameter[] pra = {
                                new SqlParameter("@ID",obj.ID)
						
                                };
            return DbHelper.ExecuteNonQuery(sql.ToString(), pra);
        }

        public List<Model.CNVP_NewsInfo> LoadAll(string sqlWhere)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select [ID],[NewsClassID],[NewsTitle],[NewsContent],[AddTime],[UpdateTime],[IsShow],[ShowTime],[IsDelete],[HotLink],[SeoInfoID] from " + Prefix + "NewsInfo ");
            if (sqlWhere.Trim() != "")
            {
                sql.Append(" where " + sqlWhere);
            }
            DataTable DT = DbHelper.ExecuteTable(sql.ToString());
            List<Model.CNVP_NewsInfo> list = new List<Model.CNVP_NewsInfo>();
            if (DT != null && DT.Rows.Count > 0)
            {
                foreach (DataRow dr in DT.Rows)
                {
                    Model.CNVP_NewsInfo cnvp_newsinfo = new Model.CNVP_NewsInfo();
                    cnvp_newsinfo.ID = dr["ID"] is DBNull ? 0 : (int)dr["ID"];
                    cnvp_newsinfo.NewsClassID = dr["NewsClassID"] is DBNull ? 0 : (int)dr["NewsClassID"];
                    cnvp_newsinfo.NewsTitle = dr["NewsTitle"] is DBNull ? String.Empty : (string)dr["NewsTitle"];
                    cnvp_newsinfo.NewsContent = dr["NewsContent"] is DBNull ? String.Empty : (string)dr["NewsContent"];
                    cnvp_newsinfo.AddTime = dr["AddTime"] is DBNull ? DateTime.Now : (DateTime)dr["AddTime"];
                    cnvp_newsinfo.UpdateTime = dr["UpdateTime"] is DBNull ? DateTime.Now : (DateTime)dr["UpdateTime"];
                    cnvp_newsinfo.IsShow = dr["IsShow"] is DBNull ? (byte)0 : (byte)dr["IsShow"];
                    cnvp_newsinfo.ShowTime = dr["ShowTime"] is DBNull ? DateTime.Now : (DateTime)dr["ShowTime"];
                    cnvp_newsinfo.IsDelete = dr["IsDelete"] is DBNull ? (byte)0 : (byte)dr["IsDelete"];
                    cnvp_newsinfo.HotLink = dr["HotLink"] is DBNull ? String.Empty : (string)dr["HotLink"];
                    cnvp_newsinfo.SeoInfoID = dr["SeoInfoID"] is DBNull ? 0 : (int)dr["SeoInfoID"];
                    list.Add(cnvp_newsinfo);
                }
            }
            return list;

        }

        public Model.CNVP_NewsInfo LoadById(int ID)
        {

            StringBuilder sql = new StringBuilder();
            sql.Append("select [NewsClassID],[NewsTitle],[NewsContent],[AddTime],[UpdateTime],[IsShow],[ShowTime],[IsDelete],[HotLink],[SeoInfoID] from " + Prefix + "NewsInfo");
            sql.Append(" where [ID] = @ID");
            SqlParameter[] pra = {
                                 new SqlParameter("@ID",ID)
						
                                    };
            DataTable DT = DbHelper.ExecuteTable(sql.ToString(), pra);
            Model.CNVP_NewsInfo cnvp_newsinfo = new Model.CNVP_NewsInfo();
            if (DT != null && DT.Rows.Count > 0)
            {
                DataRow dr = DT.Rows[0];
                cnvp_newsinfo.NewsClassID = dr["NewsClassID"] is DBNull ? 0 : (int)dr["NewsClassID"];
                cnvp_newsinfo.NewsTitle = dr["NewsTitle"] is DBNull ? String.Empty : (string)dr["NewsTitle"];
                cnvp_newsinfo.NewsContent = dr["NewsContent"] is DBNull ? String.Empty : (string)dr["NewsContent"];
                cnvp_newsinfo.AddTime = dr["AddTime"] is DBNull ? DateTime.Now : (DateTime)dr["AddTime"];
                cnvp_newsinfo.UpdateTime = dr["UpdateTime"] is DBNull ? DateTime.Now : (DateTime)dr["UpdateTime"];
                cnvp_newsinfo.IsShow = dr["IsShow"] is DBNull ? (byte)0 : (byte)dr["IsShow"];
                cnvp_newsinfo.ShowTime = dr["ShowTime"] is DBNull ? DateTime.Now : (DateTime)dr["ShowTime"];
                cnvp_newsinfo.IsDelete = dr["IsDelete"] is DBNull ? (byte)0 : (byte)dr["IsDelete"];
                cnvp_newsinfo.HotLink = dr["HotLink"] is DBNull ? String.Empty : (string)dr["HotLink"];
                cnvp_newsinfo.SeoInfoID = dr["SeoInfoID"] is DBNull ? 0 : (int)dr["SeoInfoID"];
            }
            return cnvp_newsinfo;
        }

        #endregion
        public DataTable GetSingleNewsForTemplate(int newsID)
        {
            try
            {
                string strSql = "select top 1 a.[ID] as NewsID,a.[NewsClassID],a.[NewsTitle],a.[NewsContent],a.[AddTime],a.[UpdateTime],a.[ShowTime],a.[HotLink],a.[SeoInfoID],b.OriginTitle,b.OriginKey,b.OriginDescription  from " + Prefix + "NewsInfo as a left join " + Prefix + "SEOInfo as b on a.SeoInfoID=b.ID where (a.[IsShow]=1 or a.[IsDelete]=0) and a.ID=@ID  order by a.ID DESC ";
                SqlParameter[] parm = new SqlParameter[] { new SqlParameter("@ID", newsID) };
                return DbHelper.ExecuteTable(strSql, parm);
            }
            catch (Exception e)
            {
                throw new Exception("用于显示的新闻查询的SQL语句无效:" + e.Message);
            }

        }
        public DataTable GetTopNewsForTemplate()
        {
            try
            {
                string strSql = "select top 1 a.[ID] as NewsID,a.[NewsClassID],a.[NewsTitle],a.[NewsContent],a.[AddTime],a.[UpdateTime],a.[ShowTime],a.[HotLink],a.[SeoInfoID],b.OriginTitle,b.OriginKey,b.OriginDescription  from " + Prefix + "NewsInfo as a left join " + Prefix + "SEOInfo as b on a.SeoInfoID=b.ID where a.[IsShow]=1 or a.[IsDelete]=0  order by a.ID DESC";
                return DbHelper.ExecuteTable(strSql);
            }
            catch (Exception e)
            {
                throw new Exception("用于默认显示的新闻查询的SQL语句无效:" + e.Message);
            }

        }
        public DataTable GetPage(string PageName, int PageIndex, int PageSize, out int RecordCount, out int PageCount, params SQLConditionInfo[] SqlCondition)
        {
            try
            {
                string sql_Temp = "";
                if (SqlCondition[3].value.ToString() == "0")
                {
                    if (SqlCondition[1].value.ToString() != "")
                    {
                        sql_Temp += " and a.NewsTitle like '%" + SqlCondition[1].value.ToString() + "%' ";
                    }
                }
                else
                {
                    if (SqlCondition[0].value.ToString() != "")
                    {
                        sql_Temp += " and a.NewsClassID = " + SqlCondition[0].value.ToString() + " ";
                    }
                    if (SqlCondition[2].value.ToString() != "")
                    {
                        sql_Temp += " and a.NewsTitle like '%" + SqlCondition[2].value.ToString() + "%' ";
                    }
                }

                string AllFields = "  a.ID, a.NewsClassID, a.NewsTitle, a.NewsContent, a.AddTime, a.UpdateTime, a.IsShow, a.ShowTime, a.IsDelete, a.HotLink,b.ColumnName";
                string Condition = "" + Prefix + "NewsInfo as a left join " + Prefix + "Column as b on a.NewsClassID=b.ID where 1=1" + sql_Temp + "";
                string IndexField = "a.ID";
                string OrderFields = "order by a.Id Desc";
                return DbHelper.ExecutePage(AllFields, Condition, IndexField, OrderFields, PageIndex, PageSize, out RecordCount, out PageCount, null);
            }
            catch (Exception e)
            {
                throw new Exception("用于分页的SQL语句无效:" + e.Message);
            }

        }
        public int GetTopNewsDetailID(int columnID)
        {
            string StrSql = "Select top 1 AddressRoute  From " + Prefix + "Column Where ID=@ColumnID";
            SqlParameter[] param = { DbHelper.MakeInParam("@ColumnID", SqlDbType.Int, 4, columnID) };
            DataTable dt = DbHelper.ExecuteTable(StrSql, param);
            string AddressRoute = String.Empty;
            if (dt == null || dt.Rows.Count == 0)
            {
                if (columnID == 0)
                {
                    AddressRoute = "0";
                }
                else
                {
                    AddressRoute = "0," + columnID.ToString();
                }
            }
            else
            {
                AddressRoute = dt.Rows[0]["AddressRoute"] + "," + columnID.ToString();
            }
            dt.Clear();
            StrSql = "select top 1 id from " + Prefix + "NewsInfo where NewsClassID in (" + AddressRoute + ") order by charindex(Cast(NewsClassID as VarChar(50)),'," + AddressRoute + ",') desc,id desc";
            dt = DbHelper.ExecuteTable(StrSql);
            if (dt == null || dt.Rows.Count == 0)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(dt.Rows[0]["id"]);
            }
        }
        public int UpdateHotLink(int ID, string HotLink)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("update " + Prefix + "NewsInfo set [HotLink] = @HotLink where [ID] = @ID");

            SqlParameter[] pra = { 
                            new SqlParameter("@ID",ID),
							new SqlParameter("@HotLink",HotLink)
                                };
            return DbHelper.ExecuteNonQuery(sql.ToString(), pra);
        }


    }
}

